﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using Entities;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

namespace SQLDao
{
    public class SQL_User_dao : IUserDAO
    {
        private SqlConnection connection;
        public SQL_User_dao()
        {
            var dao = new SQL_Dao();
            this.connection = SQL_Dao.Connection;
        }

        public User GetUser(int databaseID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [id], [login], [password] FROM [Users] WHERE [id]='{0}'", databaseID);

            User user = new User();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    user.ID = (int)reader["id"];
                    user.Login = (string)reader["login"];
                    user.PasswordMD5 = (string)reader["password"];
                }
                else
                {
                    // User not found
                    return new User();
                }
            }

            var roles = new List<string>();
            command.CommandText = string.Format("SELECT [title] FROM [Roles] JOIN [RolesAssign] ON [RolesAssign].[role_id] = [Roles].[id] WHERE [RolesAssign].[user_id] = '{0}'", databaseID);
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        roles.Add((string)reader["title"]);
                    }
                }
                else
                {
                    return new User();
                }
                user.Roles = roles;
                return user;
            }
        }

        public void AddUser(User user)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("INSERT INTO [Users] ([login], [password], [state]) VALUES('{0}', '{1}', '0')", user.Login, user.PasswordMD5);

            try
            {
                if (command.ExecuteNonQuery() != 1)
                {
                    // Something strange...
                }
            }
            catch (SqlException)
            {
                // Couldn't add user but we would stay silent...
            }

            int user_id = GetUserID(user.Login);

            var hack = new SQL_Role_dao();
            foreach (var role in user.Roles)
            {

                command.CommandText = string.Format("INSERT INTO [RolesAssign] ([user_id], [role_id]) VALUES ('{0}','{1}')", user_id, hack.GetRoleID(role));
                command.ExecuteNonQuery();
            }
        }

        public void RemoveUser(int databaseID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("DELETE FROM [Users] WHERE [id] = '{0}'", databaseID);
            command.ExecuteNonQuery();
        }

        public void AddRole(int userID, int roleID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("INSERT INTO [RolesAssign] ([role_id], [user_id]) VALUES('{0}', '{1}')", roleID, userID);

            try
            {
                if (command.ExecuteNonQuery() != 1)
                {
                    // Strange...
                }
            }
            catch (SqlException)
            {
            }
        }

        public void RemoveRole(int userID, int roleID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("DELETE FROM [RolesAssign] WHERE [user_id] = '{1}' AND [role_id] = '{0}'", roleID, userID);

            try
            {
                if (command.ExecuteNonQuery() != 1)
                {
                    // do nothing
                }
            }
            catch (SqlException)
            {
            }
        }

        public bool UserExists(int userID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [login] FROM [Users] WHERE [id]='{0}'", userID);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public bool UserExists(string userLogin)
        {
            return this.GetUserID(userLogin) != -1;
        }

        public int GetUserID(string userLogin)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [id] FROM [Users] WHERE [login] = '{0}'", userLogin);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return (int)reader["id"];
                }
                return -1;
            }
        }

        public bool IsValidAuth(User auth_user)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [login] FROM [Users] WHERE [login] = '{0}' AND [password] = '{1}'", auth_user.Login, auth_user.PasswordMD5);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    return true;
                }
                return false;
            }
        }

        public List<User> GetUsersForRole(string role, int limit = 0)
        {
            return null;

            //SqlCommand command = new SqlCommand();
            //command.Connection = this.connection;
            //command.CommandText = string.Format("SELECT [id] FROM [Roles] WHERE [title] = \'{0}\'", role);

            //int role_id = -1;

            //SqlDataReader reader = command.ExecuteReader();
            //if (reader.HasRows)
            //{
            //    reader.Read();
            //    role_id = (int)reader["id"];
            //}
            //else
            //{
            //    return new List<User>();
            //}

            //command.CommandText = string.Format("SELECT [Users].[id], [Users].[login], [Users].[password], [RolesAssign].[role_id] FROM [Users] JOIN [RolesAssign] ON [RolesAssign].[role_id] = \'{0}\' {1}", role_id, (limit==0)?"":string.Format("LIMIT {0}",limit));
            //reader = command.ExecuteReader();
            //if (reader.HasRows)
            //{
            //    var list = new List<User>();
            //    while (reader.Read())
            //    {
            //        int t_userId = (int)reader["id"];
            //        User t_user = new User();

            //        if (list.Count((x) => (x.ID == t_userId)) == 0)
            //        {
            //            t_user.ID = t_userId;
            //            t_user.Login = (string)reader["login"];
            //            t_user.PasswordMD5 = (string)reader["password"];
            //        }
            //    }
            //}
            //else
            //{
            //    return new List<User>();
            //}
        }   // Not implemeted correctly

        public bool UserOnInterview(int userID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [state] FROM [Users] WHERE [id]='{0}'", userID);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (!reader.HasRows)
                {
                    return false;
                }
                reader.Read();
                return ((int)reader["state"] == 1) ? true : false;
            }
        }

        public void SetUserState(int userID, bool on_interview)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("UPDATE [Users] SET [state]='{1}' WHERE [id]='{0}'", userID, on_interview ? 1 : 0);
            command.ExecuteNonQuery();
        }
    }
}
